﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;
using System.Data.OracleClient;
namespace BLL
{
    /// <summary>
    /// 操纵用户的业务类
    ///用户包括（公司，商铺，个人）
    /// </summary>
    
    public static class UserMemberBLL
    {

        public static DataSet GetUserMemberByDBLNo(string dblNo)
        {
            string sqlstr = "select * from user_member where dblno = '" + dblNo + "'";
            return DbHelperOra.Query(sqlstr);
        }

        public static DataSet GetUserMemberByID(int id)
        {
            string sqlstr = "select * from user_member where id = " + id.ToString();
            return DbHelperOra.Query(sqlstr);
        }

        public static DataSet GetUserMemberByLoginEmail(string loginEmail)
        {
            string sqlstr = "select * from V_UserBriefAll where loginemail like '%" +
                loginEmail + "%'";
            return DbHelperOra.Query(sqlstr);
        }
        
        /// <summary>
        /// 在user_member表中搜索，loginEmail是否可以被注册
        /// </summary>
        /// <returns></returns>
        public static bool IsLoginEmailAvaible(string loginEmail)
        {
            string sqlstr = "select * from user_member where lower(loginemail) = '"+loginEmail.ToLower()+"'";
            DataSet ds = DbHelperOra.Query(sqlstr);
            if (ds.Tables[0].Rows.Count == 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 在user_member表中插入数据
        /// </summary>
        /// <param name="dblNo"></param>
        /// <param name="loginEmail"></param>
        /// <param name="password"></param>
        /// <param name="userLevelID"></param>
        /// <param name="dblType"></param>
        /// <param name="userLevelStart"></param>
        /// <param name="userLevelEnd"></param>
        /// <returns></returns>
        public static int AddUserMember(string dblNo, string loginEmail, string password,
            int userLevelID, string dblType, DateTime userLevelStart, DateTime userLevelEnd)
        {
            string sqldblno = "select dblNo from user_member where dblNo = " + dblNo;
            DataSet dsDBLNo = DbHelperOra.Query(sqldblno);
            if (dsDBLNo.Tables[0].Rows.Count != 0)
            {
                //dblno已经存在，发生错误返回
                throw new ApplicationException("多巴拉号码重复:"+dblNo);
                
            }

            
            if (!IsLoginEmailAvaible(loginEmail))
            {
                throw new ApplicationException("登录Email重复:"+loginEmail);
                
            }

            if (dblType == null || dblType.Equals(""))
            {
                throw new ApplicationException("没有指定是企业、商店、个人及组织");
                
            }

            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into USER_MEMBER(");
            strSql.Append("DBLNO,LOGINEMAIL,PASSWORD,USERLEVELID,DBLTYPE,USERLEVELSTART,USERLEVELEND)");
            strSql.Append(" values (");
            strSql.Append(":DBLNO,:LOGINEMAIL,:PASSWORD,:USERLEVELID,:DBLTYPE,:USERLEVELSTART,:USERLEVELEND)");
            OracleParameter[] parameters = {
					
					new OracleParameter(":DBLNO", OracleType.VarChar,16),
					new OracleParameter(":LOGINEMAIL", OracleType.VarChar,20),
					new OracleParameter(":PASSWORD", OracleType.VarChar,20),
					new OracleParameter(":USERLEVELID", OracleType.Number,4),
					new OracleParameter(":DBLTYPE", OracleType.VarChar,1),
					new OracleParameter(":USERLEVELSTART", OracleType.DateTime),
					new OracleParameter(":USERLEVELEND", OracleType.DateTime)};
            parameters[0].Value = dblNo;
            parameters[1].Value = loginEmail;
            parameters[2].Value = password;
            parameters[3].Value = userLevelID;
            parameters[4].Value = dblType.ToUpper();
            if (userLevelStart == null)
            {
                parameters[5].Value = System.DateTime.Now;
            }
            else
            {
                parameters[5].Value = userLevelStart;
            }
            if (userLevelEnd == null)
            {
                parameters[6].Value = System.DateTime.Now.AddYears(1);//默认1年用户期限
            }
            else
            {
                parameters[6].Value = userLevelEnd;
            }

            return  DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
					
					
            
            
        }
        /// <summary>
        /// 登录
        /// </summary>
        /// <param name="loginname">可以是email，或者dblno</param>
        /// <param name="password">密码</param>
        /// <returns>
        /// 如果用户名密码验证成功返回用户id
        /// 如果失败返回失败信息
        /// </returns>
        public static string UserLogin(string loginname, string password)
        {
            string sql = string.Empty;
            if (ToolBLL.isNumberic(loginname))
            {
                //纯数字，用dblno匹配
                 sql = "select * from user_member where dblno = '" + loginname + "' and password = '" + password + "'";
                
            }
            else
            {
                //用loginemail匹配
                 sql = "select * from user_member where lower(loginemail) = '"+loginname.ToLower()+"' and password = '"+password+"'";
            }

            DataSet ds = DbHelperOra.Query(sql);
            if (ds.Tables[0].Rows.Count == 0)
            {
                //没有找到
                return "输入的登录用户或者密码不正确";
            }
            else
            {
                return ds.Tables[0].Rows[0]["id"].ToString();
            }
        }
        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="id"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public static int ChangeUserMemberPassword(int id, string password)
        {
            if ((password == null || password == ""))
            {
               return 0;
            }

            StringBuilder sb = new StringBuilder();
            sb.Append("update user_member set ");
             sb.Append("password = :v_password ");
            sb.Append ("where id = :v_id");

            OracleParameter[] para = {
                                          new OracleParameter(":v_password",OracleType.NVarChar),
                                          new OracleParameter(":v_id",OracleType.Number)
                                      };
            para[0].Value = password;
            para[1].Value = id;
            return DbHelperOra.ExecuteSql(sb.ToString(), para);

        }
        /// <summary>
        /// 更改用户权限
        /// </summary>
        /// <param name="id"></param>
        /// <param name="userLevelID"></param>
        /// <param name="userLevelStart"></param>
        /// <param name="userlevelEnd"></param>
        /// <returns></returns>
        public static int ChangeUserMemberLevel(int id ,int userLevelID,
            DateTime userLevelStart,DateTime userLevelEnd)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("update user_member set ");
            sb.Append("userlevelid = :v_userlevelid, ");
            sb.Append("userlevelstart = :v_userlevelstart,");
            sb.Append("userlevelEnd = :v_userlevelend ");
            sb.Append(" where id = :v_id");

            OracleParameter [] para = {
                                          new OracleParameter(":v_userlevelid",OracleType.Number),
                                          new OracleParameter(":v_userlevelstart",OracleType.DateTime),
                                          new OracleParameter(":v_userlevelend",OracleType.DateTime),
                                          new OracleParameter(":v_id",OracleType.Number)
                                      };
            para[0].Value = userLevelID;
            para[1].Value = userLevelStart;
            para[2].Value = userLevelEnd;
            para[3].Value = id;
            return DbHelperOra.ExecuteSql(sb.ToString(),para);
        }


        public static int DeleteUserMember(int id)
        { 
            string sql = "delete from user_member where id = "+id.ToString();
            return  DbHelperOra.ExecuteSql(sql);
        }
    }
}
